package com.ph.irp.base.verifyproject.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.verifyproject.dao.PBaseVerifyprojectMaterialDao;
import com.ph.irp.model.PBaseVerifyprojectMaterial;
import com.ph.util.DBUtil;

public class PBaseVerifyprojectMaterialDaoImpl implements
		PBaseVerifyprojectMaterialDao {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void addMaterial(PBaseVerifyprojectMaterial material)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb
				.append("insert into p_base_verifyproject_material(MaterialCode,MaterialName");
		if (material.getSpec() != null && !material.getSpec().equals("")) {
			sb.append(",Spec");
		}
		if (material.getPBaseMaterialAssistantattributetypeId() != 0) {
			sb.append(",AssistantAttributeTypeID");
		}
		if (material.getMaterialAttribute() != null
				&& !material.getMaterialAttribute().equals("")) {
			sb.append(",MaterialAttribute");
		}
		sb.append(",MeasureGroupID");
		if (material.getBasicMeasureUnit() != null
				&& !material.getBasicMeasureUnit().equals("")) {
			sb.append(",BasicMeasureUnit");
		}
		if (material.getStockMeasureUnit() != null
				&& !material.getStockMeasureUnit().equals("")) {
			sb.append(",StockMeasureUnit");
		}
		if (material.getSalesMeasureUnit() != null
				&& !material.getSalesMeasureUnit().equals("")) {
			sb.append(",SalesMeasureUnit");
		}
		if (material.getStorageMeasureUnit() != null
				&& !material.getStorageMeasureUnit().equals("")) {
			sb.append(",StorageMeasureUnit");
		}
		if (material.getPBaseVerifyprojectStorageId() != 0) {
			sb.append(",StorageID");
		}
		if (material.getPBaseVerifyprojectSupplierId() != 0) {
			sb.append(",SupplierID");
		}
		if (material.getNumberPrecision() != 0) {
			sb.append(",NumberPrecision");
		}
		if (material.getBottomStorageNum() != 0) {
			sb.append(",BottomStorageNum");
		}
		if (material.getTopStorageNum() != 0) {
			sb.append(",TopStorageNum");
		}
		if (material.getSecurityStorageNum() != 0) {
			sb.append(",SecurityStorageNum");
		}
		if (material.getMemo() != null && !material.getMemo().equals("")) {
			sb.append(",Memo");
		}
		if (material.getPBaseVerifyprojectCandiateId() != 0) {
			sb.append(",CandiateID");
		}
		if (material.getStockPrice() != 0) {
			sb.append(",StockPrice");
		}
		if (material.getSalesPrice() != 0) {
			sb.append(",SalesPrice");
		}
		sb.append(",GuaranteePeriod");
		sb.append(",PlanPrice");
		if (material.getPBaseAccountbookByStorageAccountBookId() != 0) {
			sb.append(",StorageAccountBookID");
		}
		if (material.getPBaseAccountbookBySalesInputAccountBookId() != 0) {
			sb.append(",SalesInputAccountBookID");
		}
		if (material.getPBaseAccountbookBySalesCostAccountBookId() != 0) {
			sb.append(",salesCostAccountBookID");
		}
		sb.append(") values('");
		sb.append(material.getMaterialCode());
		sb.append("','");
		sb.append(material.getMaterialName());
		sb.append("'");
		if (material.getSpec() != null && !material.getSpec().equals("")) {
			sb.append(",'");
			sb.append(material.getSpec());
			sb.append("'");
		}
		if (material.getPBaseMaterialAssistantattributetypeId() != 0) {
			sb.append(",");
			sb.append(material.getPBaseMaterialAssistantattributetypeId());
		}
		if (material.getMaterialAttribute() != null
				&& !material.getMaterialAttribute().equals("")) {
			sb.append(",'");
			sb.append(material.getMaterialAttribute());
			sb.append("'");
		}
		sb.append(",");
		sb.append(material.getPBaseMeasuregroupId());
		if (material.getBasicMeasureUnit() != null
				&& !material.getBasicMeasureUnit().equals("")) {
			sb.append(",'");
			sb.append(material.getBasicMeasureUnit());
			sb.append("'");
		}
		if (material.getStockMeasureUnit() != null
				&& !material.getStockMeasureUnit().equals("")) {
			sb.append(",'");
			sb.append(material.getStockMeasureUnit());
			sb.append("'");
		}
		if (material.getSalesMeasureUnit() != null
				&& !material.getSalesMeasureUnit().equals("")) {
			sb.append(",'");
			sb.append(material.getSalesMeasureUnit());
			sb.append("'");
		}
		if (material.getStorageMeasureUnit() != null
				&& !material.getStorageMeasureUnit().equals("")) {
			sb.append(",'");
			sb.append(material.getStorageMeasureUnit());
			sb.append("'");
		}
		if (material.getPBaseVerifyprojectStorageId() != 0) {
			sb.append(",");
			sb.append(material.getPBaseVerifyprojectStorageId());
		}
		if (material.getPBaseVerifyprojectSupplierId() != 0) {
			sb.append(",");
			sb.append(material.getPBaseVerifyprojectSupplierId());
		}
		if (material.getNumberPrecision() != 0) {
			sb.append(",");
			sb.append(material.getNumberPrecision());
		}
		if (material.getBottomStorageNum() != 0) {
			sb.append(",");
			sb.append(material.getBottomStorageNum());
		}
		if (material.getTopStorageNum() != 0) {
			sb.append(",");
			sb.append(material.getTopStorageNum());
		}
		if (material.getSecurityStorageNum() != 0) {
			sb.append(",");
			sb.append(material.getSecurityStorageNum());
		}
		if (material.getMemo() != null && !material.getMemo().equals("")) {
			sb.append(",'");
			sb.append(material.getMemo());
			sb.append("'");
		}
		if (material.getPBaseVerifyprojectCandiateId() != 0) {
			sb.append(",");
			sb.append(material.getPBaseVerifyprojectCandiateId());
		}
		if (material.getStockPrice() != 0) {
			sb.append(",");
			sb.append(material.getStockPrice());
		}
		if (material.getSalesPrice() != 0) {
			sb.append(",");
			sb.append(material.getSalesPrice());
		}
		sb.append(",");
		sb.append(material.getGuaranteePeriod());
		sb.append(",");
		sb.append(material.getPlanPrice());
		if (material.getPBaseAccountbookByStorageAccountBookId() != 0) {
			sb.append(",");
			sb.append(material.getPBaseAccountbookByStorageAccountBookId());
		}
		if (material.getPBaseAccountbookBySalesInputAccountBookId() != 0) {
			sb.append(",");
			sb.append(material.getPBaseAccountbookBySalesInputAccountBookId());
		}
		if (material.getPBaseAccountbookBySalesCostAccountBookId() != 0) {
			sb.append(",");
			sb.append(material.getPBaseAccountbookBySalesCostAccountBookId());
		}
		sb.append(")");

		try {
			// System.out.println(sb.toString());
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.execute();
		} catch (Exception e) {

			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	@Override
	public void deleteMaterial(int materialId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb
				.append(
						"delete from p_base_verifyproject_material where MaterialID=")
				.append(materialId);

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	@Override
	public List<PBaseVerifyprojectMaterial> getAllMaterial() throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseVerifyprojectMaterial> list = new ArrayList<PBaseVerifyprojectMaterial>();

		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_verifyproject_material");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseVerifyprojectMaterial pbvm = new PBaseVerifyprojectMaterial();
				pbvm.setMaterialId(rs.getInt("MaterialID"));
				pbvm.setMaterialCode(rs.getString("MaterialCode"));
				pbvm.setMaterialName(rs.getString("MaterialName"));
				pbvm.setSpec(rs.getString("Spec"));
				pbvm.setPBaseMaterialAssistantattributetypeId(rs
						.getInt("AssistantAttributeTypeID"));
				pbvm.setMaterialAttribute(rs.getString("MaterialAttribute"));
				pbvm.setPBaseMeasuregroupId(rs.getInt("MeasureGroupID"));
				pbvm.setBasicMeasureUnit(rs.getString("BasicMeasureUnit"));
				pbvm.setStockMeasureUnit(rs.getString("StockMeasureUnit"));
				pbvm.setSalesMeasureUnit(rs.getString("SalesMeasureUnit"));
				pbvm.setStorageMeasureUnit(rs.getString("StorageMeasureUnit"));
				pbvm.setPBaseVerifyprojectStorageId(rs.getInt("StorageID"));
				pbvm.setPBaseVerifyprojectSupplierId(rs.getInt("SupplierID"));
				pbvm.setNumberPrecision(rs.getInt("NumberPrecision"));
				pbvm.setBottomStorageNum(rs.getInt("BottomStorageNum"));
				pbvm.setTopStorageNum(rs.getInt("TopStorageNum"));
				pbvm.setSecurityStorageNum(rs.getInt("SecurityStorageNum"));
				pbvm.setMemo(rs.getString("Memo"));
				pbvm.setPBaseVerifyprojectCandiateId(rs.getInt("CandiateID"));
				pbvm.setStockPrice(rs.getDouble("StockPrice"));
				pbvm.setSalesPrice(rs.getDouble("SalesPrice"));
				pbvm.setGuaranteePeriod(rs.getInt("GuaranteePeriod"));
				pbvm.setPlanPrice(rs.getDouble("PlanPrice"));
				pbvm.setPBaseAccountbookByStorageAccountBookId(rs
						.getInt("StorageAccountBookID"));
				pbvm.setPBaseAccountbookBySalesInputAccountBookId(rs
						.getInt("SalesInputAccountBookID"));
				pbvm.setPBaseAccountbookBySalesCostAccountBookId(rs
						.getInt("SalesCostAccountBookID"));
				list.add(pbvm);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return list;
	}

	@Override
	public PBaseVerifyprojectMaterial getMaterialById(int materialId)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseVerifyprojectMaterial material = null;

		StringBuilder sb = new StringBuilder();
		sb
				.append(
						"select * from p_base_verifyproject_material where MaterialID=")
				.append(materialId);

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			if (rs.next()) {
				material = new PBaseVerifyprojectMaterial();
				material.setMaterialId(rs.getInt("MaterialID"));
				material.setMaterialCode(rs.getString("MaterialCode"));
				material.setMaterialName(rs.getString("MaterialName"));
				material.setSpec(rs.getString("Spec"));
				material.setPBaseMaterialAssistantattributetypeId(rs
						.getInt("AssistantAttributeTypeID"));
				material
						.setMaterialAttribute(rs.getString("MaterialAttribute"));
				material.setPBaseMeasuregroupId(rs.getInt("MeasureGroupID"));
				material.setBasicMeasureUnit(rs.getString("BasicMeasureUnit"));
				material.setStockMeasureUnit(rs.getString("StockMeasureUnit"));
				material.setSalesMeasureUnit(rs.getString("SalesMeasureUnit"));
				material.setStorageMeasureUnit(rs
						.getString("StorageMeasureUnit"));
				material.setPBaseVerifyprojectStorageId(rs.getInt("StorageID"));
				material.setPBaseVerifyprojectSupplierId(rs
						.getInt("SupplierID"));
				material.setNumberPrecision(rs.getInt("NumberPrecision"));
				material.setBottomStorageNum(rs.getInt("BottomStorageNum"));
				material.setTopStorageNum(rs.getInt("TopStorageNum"));
				material.setSecurityStorageNum(rs.getInt("SecurityStorageNum"));
				material.setMemo(rs.getString("Memo"));
				material.setPBaseVerifyprojectCandiateId(rs
						.getInt("CandiateID"));
				material.setStockPrice(rs.getDouble("StockPrice"));
				material.setSalesPrice(rs.getDouble("SalesPrice"));
				material.setGuaranteePeriod(rs.getInt("GuaranteePeriod"));
				material.setPlanPrice(rs.getDouble("PlanPrice"));
				material.setPBaseAccountbookByStorageAccountBookId(rs
						.getInt("StorageAccountBookID"));
				material.setPBaseAccountbookBySalesInputAccountBookId(rs
						.getInt("SalesInputAccountBookID"));
				material.setPBaseAccountbookBySalesCostAccountBookId(rs
						.getInt("SalesCostAccountBookID"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return material;
	}

	@Override
	public List<PBaseVerifyprojectMaterial> getMaterialList(
			PBaseVerifyprojectMaterial material) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseVerifyprojectMaterial> list = new ArrayList<PBaseVerifyprojectMaterial>();

		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_verifyproject_material where 1=1");
		if (material != null) {
			if (material.getMaterialCode() != null
					&& !material.getMaterialCode().equals("")) {
				sb.append(" and MaterialCode like '%");
				sb.append(material.getMaterialCode());
				sb.append("%'");
			}
			if (material.getMaterialName() != null
					&& !material.getMaterialName().equals("")) {
				sb.append(" and MaterialName like '%");
				sb.append(material.getMaterialName());
				sb.append("%'");
			}
			if (material.getPBaseVerifyprojectStorageId() != 0) {
				sb.append(" and StorageID=");
				sb.append(material.getPBaseVerifyprojectStorageId());
			}
			if (material.getMaterialAttribute() != null
					&& !material.getMaterialAttribute().equals("")) {
				sb.append(" and MaterialAttribute like '%");
				sb.append(material.getMaterialAttribute());
				sb.append("%'");
			}
		}
		// System.out.println(sb.toString());
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseVerifyprojectMaterial pbvm = new PBaseVerifyprojectMaterial();
				pbvm.setMaterialId(rs.getInt("MaterialID"));
				pbvm.setMaterialCode(rs.getString("MaterialCode"));
				pbvm.setMaterialName(rs.getString("MaterialName"));
				pbvm.setSpec(rs.getString("Spec"));
				pbvm.setPBaseMaterialAssistantattributetypeId(rs
						.getInt("AssistantAttributeTypeID"));
				pbvm.setMaterialAttribute(rs.getString("MaterialAttribute"));
				pbvm.setPBaseMeasuregroupId(rs.getInt("MeasureGroupID"));
				pbvm.setBasicMeasureUnit(rs.getString("BasicMeasureUnit"));
				pbvm.setStockMeasureUnit(rs.getString("StockMeasureUnit"));
				pbvm.setSalesMeasureUnit(rs.getString("SalesMeasureUnit"));
				pbvm.setStorageMeasureUnit(rs.getString("StorageMeasureUnit"));
				pbvm.setPBaseVerifyprojectStorageId(rs.getInt("StorageID"));
				pbvm.setPBaseVerifyprojectSupplierId(rs.getInt("SupplierID"));
				pbvm.setNumberPrecision(rs.getInt("NumberPrecision"));
				pbvm.setBottomStorageNum(rs.getInt("BottomStorageNum"));
				pbvm.setTopStorageNum(rs.getInt("TopStorageNum"));
				pbvm.setSecurityStorageNum(rs.getInt("SecurityStorageNum"));
				pbvm.setMemo(rs.getString("Memo"));
				pbvm.setPBaseVerifyprojectCandiateId(rs.getInt("CandiateID"));
				pbvm.setStockPrice(rs.getDouble("StockPrice"));
				pbvm.setSalesPrice(rs.getDouble("SalesPrice"));
				pbvm.setGuaranteePeriod(rs.getInt("GuaranteePeriod"));
				pbvm.setPlanPrice(rs.getDouble("PlanPrice"));
				pbvm.setPBaseAccountbookByStorageAccountBookId(rs
						.getInt("StorageAccountBookID"));
				pbvm.setPBaseAccountbookBySalesInputAccountBookId(rs
						.getInt("SalesInputAccountBookID"));
				pbvm.setPBaseAccountbookBySalesCostAccountBookId(rs
						.getInt("SalesCostAccountBookID"));
				list.add(pbvm);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return list;
	}

	@Override
	public boolean isSame(String materialCode) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		String sql = "select * from p_base_verifyproject_material where MaterialCode='"
				+ materialCode.trim() + "'";

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			rs = pStmt.executeQuery();
			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	@Override
	public void updateMaterial(PBaseVerifyprojectMaterial material)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append("update p_base_verifyproject_material set MaterialCode='");
		sb.append(material.getMaterialCode()).append("',MaterialName='");
		sb.append(material.getMaterialName()).append("'");
		// if(material.getSpec() != null && !material.getSpec().equals("")){
		sb.append(",Spec='").append(material.getSpec()).append("'");
		// }
		// if(material.getPBaseMaterialAssistantattributetypeId() != 0){
		// System.out.println(material.getPBaseMaterialAssistantattributetypeId());

		sb.append(",AssistantAttributeTypeID=").append(
				material.getPBaseMaterialAssistantattributetypeId());
		// }
		// if(material.getMaterialAttribute() != null &&
		// !material.getMaterialAttribute().equals("")){
		sb.append(",MaterialAttribute='").append(
				material.getMaterialAttribute()).append("'");
		// }
		sb.append(",MeasureGroupID=").append(material.getPBaseMeasuregroupId());
		// if(material.getBasicMeasureUnit() != null &&
		// !material.getBasicMeasureUnit().equals("")){
		sb.append(",BasicMeasureUnit='").append(material.getBasicMeasureUnit())
				.append("'");
		// }
		// if(material.getStockMeasureUnit() != null &&
		// !material.getStockMeasureUnit().equals("")){
		sb.append(",StockMeasureUnit='").append(material.getStockMeasureUnit())
				.append("'");
		// }
		// if(material.getSalesMeasureUnit() != null &&
		// !material.getSalesMeasureUnit().equals("")){
		sb.append(",SalesMeasureUnit='").append(material.getSalesMeasureUnit())
				.append("'");
		// }
		// if(material.getStorageMeasureUnit() != null &&
		// !material.getStorageMeasureUnit().equals("")){
		sb.append(",StorageMeasureUnit='").append(
				material.getStorageMeasureUnit()).append("'");
		// }
		if (material.getPBaseVerifyprojectStorageId() != 0) {
			sb.append(",StorageID=").append(
					material.getPBaseVerifyprojectStorageId());
		}
		if (material.getPBaseVerifyprojectSupplierId() != 0) {
			sb.append(",SupplierID=").append(
					material.getPBaseVerifyprojectSupplierId());
		}
		if (material.getNumberPrecision() != 0) {
			sb.append(",NumberPrecision=")
					.append(material.getNumberPrecision());
		}
		sb.append(",BottomStorageNum=").append(material.getBottomStorageNum());
		sb.append(",TopStorageNum=").append(material.getTopStorageNum());
		sb.append(",SecurityStorageNum=").append(
				material.getSecurityStorageNum());
		// if(material.getMemo() != null && !material.getMemo().equals("")){
		sb.append(",Memo='").append(material.getMemo()).append("'");
		// }
		if (material.getPBaseVerifyprojectCandiateId() != 0) {
			sb.append(",CandiateID=").append(
					material.getPBaseVerifyprojectCandiateId());
		}
		// if(material.getStockPrice() != 0){
		sb.append(",StockPrice=").append(material.getStockPrice());
		// }
		// if(material.getSalesPrice() != 0){
		sb.append(",SalesPrice=").append(material.getSalesPrice());
		// }
		sb.append(",GuaranteePeriod=").append(material.getGuaranteePeriod());
		sb.append(",PlanPrice=").append(material.getPlanPrice());
		if (material.getPBaseAccountbookByStorageAccountBookId() != 0) {
			sb.append(",StorageAccountBookID=").append(
					material.getPBaseAccountbookByStorageAccountBookId());
		}
		if (material.getPBaseAccountbookBySalesInputAccountBookId() != 0) {
			sb.append(",SalesInputAccountBookID=").append(
					material.getPBaseAccountbookBySalesInputAccountBookId());
		}
		if (material.getPBaseAccountbookBySalesCostAccountBookId() != 0) {
			sb.append(",SalesCostAccountBookID=").append(
					material.getPBaseAccountbookBySalesCostAccountBookId());
		}
		sb.append(" where MaterialID=").append(material.getMaterialId());

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

}
